﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Xml;
using SdmxMl.SmsImportCfg;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;
using SdmxMl.GenericMetadata;

namespace SilcQuest
{
    public class MisCodes
    {
         private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;

        private ConceptSchemeType cst;
        public SmManager sm;
        private const string AgencyId = "CH1_SILC";

        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }

        #region SAKE Vars

        public MisCodes()
        {
            spreadsheetConcept = LoadSources(@"C:\Doc\SAKE\Main_variables.xls");

       }
        #endregion

        private string CleanCodeText(string txt)
        {
          //  string codeText = txt.Replace("\"", "");
          //  int ndx = codeText.IndexOf('.');
          //  return codeText.Substring(ndx+1);
            return txt.Trim();
        }

        public void ConstructCodes()
        {
            sm = ReferenceManager.GetSmContainerImage();

            spreadsheetConcept = LoadSources(@"C:\Doc\SILC\GRILLE W2 VALUE.xls");
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);
            CodeListType cltCode = null;
            CodeListType cltQuality = null;

            string previousVarId = string.Empty;

            for (int row = 2; row < dt.Rows.Count; row++)
            {
                    string varId = wh.GetCellContent(row, 3);
                    if (varId != previousVarId)
                    {
                        previousVarId = varId;

                        UpdateCodelistContainer(cltCode, cltQuality, previousVarId);
                        // cOwner = cst.ConceptList.LocateItem(varId.ToUpper()) as ConceptType;
                        cltCode = new CodeListType();
                        cltCode.Id = "CL_" + varId.ToUpper();
                        cltCode.AgencyId = AgencyId;
                        cltCode.Name.UpdateDefaultText("Codelist for : " + varId.ToUpper());

                        cltQuality = new CodeListType();
                        cltQuality.Id = "CL_QUAL_" + varId.ToUpper();
                        cltQuality.AgencyId = AgencyId;
                        cltQuality.Name.UpdateDefaultText("Quality Codelist for : " + varId.ToUpper());
                    }

                    string code = wh.GetCellContent(row, 2);
                    string de = wh.GetCellContent(row, 6);
                    string fr = wh.GetCellContent(row, 5);
                    string it = wh.GetCellContent(row, 7);
                    string en = wh.GetCellContent(row, 4);

                    CodeType c = new CodeType();
                    c.Id = code;
                    c.Name.UpdateForLanguage("de", CleanCodeText(de));
                    c.Name.UpdateForLanguage("fr", CleanCodeText(fr));
                    c.Name.UpdateForLanguage("it", CleanCodeText(it));
                    c.Name.UpdateForLanguage("en", CleanCodeText(en));

                    if (code.Length > 0 && code[0] == '-')
                        cltQuality.CodeList.Add(c);
                    else
                        cltCode.CodeList.Add(c);
           }
            UpdateCodelistContainer(cltCode, cltQuality, previousVarId);

            XmlDocument xDoc = sm.GetAsDocument();
            xDoc.Save(@"C:\doc\SILC\" + "CL_W2" + ".xml");


        }

        private void UpdateCodelistContainer(CodeListType cltCode, CodeListType cltQuality, string varId)
        {
            CodeListType clt = null;

            if (cltCode != null && cltCode.CodeList.Count > 0 )
             //   if (cltCode != null && cltCode.CodeList.Count > 0 && !(cltQuality != null && cltQuality.CodeList.Count == cltCode.CodeList.Count))
            {
                clt = CodelistAlreadyDefined(cltCode);
                if (clt != null)
                    clt.Name.UpdateDefaultText(clt.Name.ToString() + ", " + varId);
                else
                {
                    clt = cltCode;
                    sm.PutArtefact(SdmxArtefactType.CodeLists, cltCode);
                }
            }

           
            clt = null;

             if (cltQuality != null && cltQuality.CodeList.Count > 0 )
           //     if (cltQuality != null && cltQuality.CodeList.Count > 0 && (cltCode == null || cltQuality.CodeList.Count == cltCode.CodeList.Count))
            {
                clt = CodelistAlreadyDefined(cltQuality);
                if (clt != null)
                    clt.Name.UpdateDefaultText(clt.Name.ToString() + ", " + varId);
                else
                {
                    clt = cltQuality;
                    sm.PutArtefact(SdmxArtefactType.CodeLists, cltQuality);
                }
            }
             
            
        }

        private CodeListType CodelistAlreadyDefined(CodeListType newCodelist)
        {
            ArtefactManager amgr = sm.GetArtefactManager(SdmxArtefactType.CodeLists);
            foreach (CodeListType clt in amgr.ArtefactList)
                if (SameCodes(newCodelist.CodeList, clt.CodeList))
                    return clt;
            return null;
        }

        private bool SameCodes(CodeList a, CodeList b)
        {
            bool sameCodes = false;
            if (a.Count == b.Count)
            {
                sameCodes = true;
                for (int c = 0; c < a.Count; c++)
                {
                    if (a[c].Id != b[c].Id)
                    {
                        sameCodes = false;
                        break;
                    }
                    else
                    {
                        foreach (string lg in LangSupport.SupportedLanguages)
                        {
                            if (a[c].Name.GetForLanguage(lg) != b[c].Name.GetForLanguage(lg))
                            {
                                sameCodes = false;
                                break;
                            }
                        }
                        if (sameCodes == false)
                            break;
                    }
                }
            }

            return sameCodes;
        }

        public void ConstructAnnot(ConceptSchemeType conceptscheme, string propertyPath)
        {
            cst = conceptscheme;
            sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.Concepts, cst);

            spreadsheetConcept = LoadSources(propertyPath);

            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);
            string previousVarId = string.Empty;

            for (int row = 1; row <= dt.Rows.Count; row++)
            {
                string varId = wh.GetCellContent(row, 2);
                if (varId.Length > 0)
                {
                    ConceptType c = cst.ConceptList.LocateItem(varId) as ConceptType;
                    if (c != null)
                    {
                        // Parent reattribution
                        string newParent = wh.GetCellContent(row, 3).ToUpper();
                        if (newParent.Length > 0)
                        {
                            c.Parent = newParent;
                        }
                        
                        // Quest Print
                        string qp = wh.GetCellContent(row, 74);
                        if (qp.Length > 0)
                        {
                            Annotation a = new Annotation("QUEST_PRINT");
                            a.Title = qp;
                            c.AnnotationList.Add(a);
                        }
                        
                        /*
                        // TextFormat
                        string len = wh.GetCellContent(row, 5);

                        c.TextFormat.maxLength = len;
                        string fmt = wh.GetCellContent(row, 6);
                        if (fmt.Length > 0 && fmt[0] == '.')
                        {
                            c.TextFormat.TextType = TextTypeType.Decimal;
                            c.TextFormat.decimals = fmt.Substring(1);
                        }
                        else
                        {
                            c.TextFormat.TextType = TextTypeType.Integer;
                        }


                        // Add annotations
                        string indic_d = wh.GetCellContent(row, 7);
                        string indic_f = wh.GetCellContent(row, 8);
                        if (indic_d.Length > 0 || indic_f.Length > 0)
                        {
                            Annotation a = new Annotation("INDIC_SUP");
                            a.Text.UpdateForLanguage("de", indic_d);
                            a.Text.UpdateForLanguage("fr", indic_f);
                            c.AnnotationList.Add(a);
                        }

                        string vague = wh.GetCellContent(row,33);
                        if (vague.Length > 0)
                        {
                            Annotation a = new Annotation("VAGUE");
                            a.Title = vague;
                            c.AnnotationList.Add(a);
                            indic_d = wh.GetCellContent(row, 31);
                            indic_f = wh.GetCellContent(row, 32);
                            if (indic_d.Length > 0 || indic_f.Length > 0)
                            {
                                a.Text.UpdateForLanguage("de", indic_d);
                                a.Text.UpdateForLanguage("fr", indic_f);

                            }
                        }


                        indic_d = wh.GetCellContent(row, 34);
                        indic_f = wh.GetCellContent(row, 35);
                        if (indic_d.Length > 0 || indic_f.Length > 0)
                        {
                            Annotation a = new Annotation("GROUP");
                            a.Text.UpdateForLanguage("de", indic_d);
                            a.Text.UpdateForLanguage("fr", indic_f);
                            c.AnnotationList.Add(a);
                        }

                        // EU or CH
                        vague = wh.GetCellContent(row, 36);
                        if (vague.Length > 0)
                        {
                            Annotation a = new Annotation("REMTECH");
                            a.Title = vague;
                            c.AnnotationList.Add(a);
                        }

                        // Questions link 18 -22
                        for (int i = 40; i <= 55; ++i)
                        {
                            string quest = wh.GetCellContent(row, i);
                            if (quest.Length > 0)
                            {
                                Annotation a = new Annotation("QUEST_LNK");
                                a.Title = quest;
                                c.AnnotationList.Add(a);
                                string u = "urn:sdmx:org.sdmx.infomodel.metadataset.TargetValues=CH1_SAKE:MS_Q_MAIN[2012.0].FT_QUEST_VAR.E.2012.";
                                try
                                {
                                  //  string id = string.Format("{0:d5}", int.Parse(quest.Replace(".", string.Empty)));
                                    a.Url = new Uri(u + quest);
                                }
                                catch { }
                            }
                        }
                        */
                    }
                }
            }

        }

    }

}
